package com.cms.biz.business.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cms.biz.business.CMSAccreditationContractServiceMgr;
import com.cms.biz.business.DeliveryNoticeServiceMgr;
import com.cms.persistence.contractManager.accreditationContract.PSHTMX;
import com.common.util.DateUtil;
import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.dctbuilder.rule.EMPCodeRuler;
import com.efounder.dict.DMTServiceMgr;
import com.efounder.dict.FCTServiceMgr;
import com.efounder.eai.data.JParamObject;
import com.efounder.eai.data.JResponseObject;
import com.efounder.form.server.resolver.util.SYS_MDL_VAL;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.framework.sys.business.AbstractBusinessObjectServiceMgr;
import com.metadata.biz.MetaDataServiceMgr;
import com.metadata.bizmodel.SYS_MODEL;
import com.pub.util.StringFunction;

import dwz.dal.common.sequence.UUIDGenerator;

@Transactional(rollbackFor = Exception.class)
@Service("DeliveryNoticeServiceMgr")
public class DeliveryNoticeMgrImpl extends AbstractBusinessObjectServiceMgr implements DeliveryNoticeServiceMgr {
	
	@Autowired
	private JdbcTemplate                    jdbcTemplate;
	
	@Autowired
	private MetaDataServiceMgr         metaDataServiceMgr;

	@Autowired
	private DMTServiceMgr                      DMTService;
	
	@Autowired
	private FCTServiceMgr                      FCTService;
	
	public EFRowSet loadNewCCTZForm(JParamObject PO) {
		JConnection      conn = null;
		JStatement       stmt = null;
		ResultSet          rs = null;
		String         strSql = "";
		EFDataSet     dataset = EFDataSet.getInstance();
		EFRowSet   headRowset = EFRowSet.getInstance();
		String         F_HTBH = PO.GetValueByParamName("F_HTBH", "");
		String         F_XMBH = PO.GetValueByParamName("F_XMBH", "");
		String         F_KJQJ = PO.GetValueByParamName("F_KJQJ", "");
		String         F_GUID = PO.GetValueByParamName("F_GUID", "");
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();

			//形成头信息
			strSql = "select '" + F_KJQJ + "' as F_KJQJ, '" + F_GUID + "' as F_GUID, SCTZXX.F_HTBH,max(HTXXZD.F_HTMC) as F_HTBHMC,SCTZXX.F_XMBH,"
				   + " max(HTXMZD.F_XMMC) as F_XMBHMC, max(SCTZXX.F_JSQD) as F_JSQD,max(SCTZXX.F_SFCQ) as F_SFCQ, max(SCTZXX.F_JSFZR) as F_JSFZR, max(HYZGZD.F_ZGMC) AS F_JSFZRMC, "
				   + " max(SCTZXX.F_HTQK) as F_HTQK, max(SCTZXX.F_JHQK) as F_JHQK,max(SCTZXX.F_ZJLY) as F_ZJLY from SCTZXX"
				   + " LEFT JOIN HTXMZD ON SCTZXX.F_XMBH = HTXMZD.F_XMBH"
				   + " LEFT JOIN HTXXZD ON SCTZXX.F_HTBH = HTXXZD.F_HTBH"
				   + " LEFT JOIN HYZGZD ON SCTZXX.F_JSFZR = HYZGZD.F_ZGBH"
				   + " where SCTZXX.F_HTBH = '" + F_HTBH + "' and SCTZXX.F_XMBH = '" + F_XMBH + "' and SCTZXX.F_DJZT = '1' "
				   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.FLOW_ID = 'SCTZ_FLOW' " 
				   + " and FLOW_TASK_END_LIST.BIZ_GUID = SCTZXX.F_GUID)"
				   + " group by SCTZXX.F_HTBH,SCTZXX.F_XMBH";
			rs = stmt.executeQuery(strSql);
			if(rs != null && rs.next()) DataSetUtils.resultSet2RowSet(rs, headRowset);
			
			//形成明细信息
			strSql = "select '" + F_KJQJ + "' as F_KJQJ, '" + F_GUID + "' as F_GUID, SCTZMX.F_XMBH,max(HTXMZD.F_XMMC) as F_XMBHMC," 
			       + " SCTZMX.F_CPBH,max(HTCPZD.F_CPMC) as F_CPBHMC, max(HTCPZD.F_GGXH) as F_GGXH, max(HTCPZD.F_JLDW) as F_JLDW,"
				   + " round(sum(SCTZMX.F_SCSL), 2) as F_SCSL, SCSYSL_V.F_CCSL as F_CCSL, 0 as F_SYSL "
				   + " from SCTZMX"
				   + " LEFT JOIN HTXMZD ON SCTZMX.F_XMBH = HTXMZD.F_XMBH"
				   + " LEFT JOIN HTCPZD ON SCTZMX.F_CPBH = HTCPZD.F_CPBH"
				   + " LEFT JOIN SCSYSL_V ON SCTZMX.F_XMBH = SCSYSL_V.F_XMBH and SCTZMX.F_CPBH = SCSYSL_V.F_CPBH and SCSYSL_V.F_HTBH = '" + F_HTBH + "'"
				   + " where exists(select 1 from SCTZXX where SCTZXX.F_DJBH = SCTZMX.F_DJBH and SCTZXX.F_KJQJ = SCTZMX.F_KJQJ and " 
				   + " SCTZXX.F_GUID = SCTZMX.F_GUID  and SCTZXX.F_HTBH = '" + F_HTBH + "' and SCTZXX.F_XMBH = '" + F_XMBH + "' and "
				   + " SCTZXX.F_DJZT = '1')"
				   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.FLOW_ID = 'SCTZ_FLOW' and " 
				   + " FLOW_TASK_END_LIST.BIZ_GUID = SCTZMX.F_GUID)"
				   + " group by SCTZMX.F_XMBH,SCTZMX.F_CPBH"
				   + " order by SCTZMX.F_XMBH,SCTZMX.F_CPBH";
			rs = stmt.executeQuery(strSql);
			dataset = DataSetUtils.resultSet2DataSet(rs, dataset);
			headRowset.setDataSet("CCTZMX", dataset);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return headRowset;
	}

	@Override
	public EFRowSet loadEditCCTZForm(JParamObject PO) {
		JConnection      conn = null;
		JStatement       stmt = null;
		ResultSet          rs = null;
		String         strSql = "";
		EFDataSet     dataset = EFDataSet.getInstance();
		EFDataSet   scdataset = EFDataSet.getInstance();
		EFRowSet   headRowset = EFRowSet.getInstance();
		EFRowSet     scRowset = EFRowSet.getInstance();
        String         F_HTBH = "";
		String         F_GUID = PO.GetValueByParamName("F_GUID", "");
		double           scsl = 0.0;
		double           ccsl = 0.0;
		double           sysl = 0.0;
		String[]     primeKey = null;
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();

			//形成头信息
			strSql = "select CCTZXX.F_CHDATE as F_CHDATE,CCTZXX.F_CRDATE as F_CRDATE,CCTZXX.F_DJBH as F_DJBH,CCTZXX.F_DJZT as F_DJZT," 
				   + "CCTZXX.F_GUID as F_GUID,CCTZXX.F_HTBH as F_HTBH,F_HTBH.F_HTMC as F_HTBHMC,CCTZXX.F_JSFZR as F_JSFZR," 
				   + "F_JSFZR.F_ZGMC as F_JSFZRMC,CCTZXX.F_JSQD as F_JSQD,CCTZXX.F_KJQJ as F_KJQJ,CCTZXX.F_NOTE as F_NOTE," 
				   + "CCTZXX.F_SFCQ as F_SFCQ,CCTZXX.F_WGRQ as F_WGRQ,CCTZXX.F_XMBH as F_XMBH,F_XMBH.F_XMMC as F_XMBHMC," 
				   + "CCTZXX.F_ZDR as F_ZDR,F_ZDR.USER_NAME as F_ZDRMC,CCTZXX.F_ZDRQ as F_ZDRQ,CCTZXX.F_HTQK as F_HTQK,"
				   + "CCTZXX.F_JHQK as F_JHQK,CCTZXX.F_ZJLY as F_ZJLY,CCTZXX.F_CODE as F_CODE" 
				   + " from CCTZXX  " 
				   + "LEFT JOIN HTXXZD F_HTBH ON F_HTBH.F_HTBH = CCTZXX.F_HTBH " 
				   + "LEFT JOIN HYZGZD F_JSFZR ON F_JSFZR.F_ZGBH = CCTZXX.F_JSFZR " 
				   + "LEFT JOIN HTXMZD F_XMBH ON F_XMBH.F_XMBH = CCTZXX.F_XMBH " 
				   + "LEFT JOIN BSUSER F_ZDR ON F_ZDR.USER_ID = CCTZXX.F_ZDR " 
				   + "where  CCTZXX.F_GUID = '" + F_GUID + "' " 
				   + "order by CCTZXX.F_DJBH";
			rs = stmt.executeQuery(strSql);
			if(rs != null && rs.next()) DataSetUtils.resultSet2RowSet(rs, headRowset);

            F_HTBH = headRowset.getString("F_HTBH", "");

			//形成出厂通知单明细信息
			strSql = " select CCTZMX.F_CHDATE as F_CHDATE,CCTZMX.F_CPBH as F_CPBH,F_CPBH.F_CPMC as F_CPBHMC,CCTZMX.F_CRDATE as F_CRDATE," 
				   + " CCTZMX.F_DJBH as F_DJBH,CCTZMX.F_FLBH as F_FLBH,CCTZMX.F_GUID as F_GUID," 
				   + " CCTZMX.F_KJQJ as F_KJQJ,CCTZMX.F_CCSL as F_CCSL,CCTZMX.F_XMBH as F_XMBH,F_XMBH.F_XMMC as F_XMBHMC, " 
				   + " F_CPBH.F_GGXH as F_GGXH, F_CPBH.F_JLDW as F_JLDW, SCSYSL_V.F_CCSL as F_YCCSL" 
				   + " from CCTZMX  " 
				   + " LEFT JOIN HTCPZD F_CPBH ON F_CPBH.F_CPBH = CCTZMX.F_CPBH " 
				   + " LEFT JOIN HTXMZD F_XMBH ON F_XMBH.F_XMBH = CCTZMX.F_XMBH " 
				   + " LEFT JOIN SCSYSL_V ON CCTZMX.F_XMBH = SCSYSL_V.F_XMBH and CCTZMX.F_CPBH = SCSYSL_V.F_CPBH and SCSYSL_V.F_HTBH = '" + F_HTBH + "'"
				   + " where  CCTZMX.F_GUID = '" + F_GUID + "' " 
				   + " order by F_DJBH,CCTZMX.F_FLBH";

			rs = stmt.executeQuery(strSql);
			dataset = DataSetUtils.resultSet2DataSet(rs, dataset);
			
			
			//形成生产通知单明细信息
			strSql = "select SCTZMX.F_XMBH,SCTZMX.F_CPBH, round(sum(SCTZMX.F_SCSL), 2) as F_SCSL "
				   + " from SCTZMX"
				   + " where exists(select 1 from SCTZXX where SCTZXX.F_DJBH = SCTZMX.F_DJBH and SCTZXX.F_KJQJ = SCTZMX.F_KJQJ and " 
				   + " SCTZXX.F_GUID = SCTZMX.F_GUID  and SCTZXX.F_HTBH = '" + headRowset.getString("F_HTBH", "") + "' and SCTZXX.F_XMBH = '" 
				   + headRowset.getString("F_XMBH", "") + "' and "
				   + " SCTZXX.F_DJZT = '1')"
				   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.FLOW_ID = 'SCTZ_FLOW' and " 
				   + " FLOW_TASK_END_LIST.BIZ_GUID = SCTZMX.F_GUID)"
				   + " group by SCTZMX.F_XMBH,SCTZMX.F_CPBH"
				   + " order by SCTZMX.F_XMBH,SCTZMX.F_CPBH";
			rs = stmt.executeQuery(strSql);
			scdataset = DataSetUtils.resultSet2DataSet(rs, scdataset);
			scdataset.setPrimeKey(new String[]{"F_XMBH", "F_CPBH"});
			scdataset.buildPrimeKeyIndex();
			
			for(int i = 0; i < dataset.getRowCount(); i++) {
				primeKey = new String[]{dataset.getRowSet(i).getString("F_XMBH", ""), dataset.getRowSet(i).getString("F_CPBH", "")};
				scRowset = (EFRowSet) scdataset.getRowSet(primeKey);
				
				if(scRowset != null) {
					scsl = scRowset.getNumber("F_SCSL", 0.0).doubleValue();
					dataset.getRowSet(i).putString("F_SCSL", String.valueOf(scRowset.getNumber("F_SCSL", 0.0)));
				}
				ccsl = dataset.getRowSet(i).getNumber("F_YCCSL", 0.0).doubleValue();
				sysl = scsl - ccsl;
				dataset.getRowSet(i).putString("F_SYSL", String.valueOf(sysl));
			}
			
			headRowset.setDataSet("CCTZMX", dataset);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return headRowset;
	}

}
